package com.STS.ELearning.DAO
{
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.filesystem.FileMode;
	import flash.filesystem.FileStream;
	
	import mx.collections.ArrayCollection;
	import mx.collections.ArrayList;
	import mx.controls.Alert;
	import mx.utils.StringUtil;

	public class BaseDao
	{
		public static var dbFile:File = File.applicationStorageDirectory.resolvePath("COTData.db"); 
		private var TableName:String="Ayarlar";
		public function BaseDao(tableName:String)
		{
			TableName=tableName;
			if(!dbFile.exists)
			{
				var stmt:SQLStatement=new SQLStatement();
				var sqf:File=File.applicationDirectory.resolvePath("db.sql");
				if(!sqf.exists)
				{
					throw new Error("Veri Tabanı yapılandırma dosyası mevcut değil.Lütfen 'db.sql' dosyasını programın çalışma dizinine yerleştiriniz.");
				}
				var sqstr:FileStream=new FileStream();
				sqstr.open(sqf,FileMode.READ);
				var txtsql:String=sqstr.readUTFBytes(sqf.size);
				sqstr.close();
				
				
				var arrsql:Array=txtsql.split("\n");
				for each(var s:String in arrsql)
				{
					s=StringUtil.trim(s);
					if(s!="" && s.substring(0,1)!="#" &&  s.substr(0,2)!="/*" && s.substr(0,2)!="*/")
					{
						stmt.text=s;	
						activateCon();
						stmt.sqlConnection=sqlConnection;
						try{
						stmt.execute();
						}catch(err:Error)
						{
							trace(s+" "+err.message)
						}
						deActivateCon();
					}
				}
			}
		}
		protected var sqlConnection:SQLConnection;
		protected function activateCon():void
		{
			
			
			if(sqlConnection==null)
				sqlConnection=new SQLConnection();
			if(!sqlConnection.connected)
				sqlConnection.open(dbFile);
		
		}
		protected function deActivateCon():void
		{
			if(sqlConnection.connected && !sqlConnection.inTransaction)
				sqlConnection.close();
		}
		public function count(c:String=null):int
		{
			var stmt:SQLStatement=adjustBase();
			stmt.text = "Select count(*) from "+TableName+" ";
			if(c!=null)
			{
				stmt.text+=c;
			}
			var son:int=0;
			stmt.execute();
			var res:SQLResult= stmt.getResult();
			if(res.data!=null)
			{
				son=int(res.data[0]["count(*)"]);
			}
			deActivateCon();
			return son;
		}
		protected function getId():String
		{
			var id:String="1";
			if(count()>0)
			{
				id="((SELECT max(id) FROM main."+TableName+")+1)";
			}
			return id;
		}
		
		public function getSingleRow(where:String=null,whereParams:Array=null):Object
		{
				var data:Array=getTableContent(null,where,whereParams);
				if(data!=null && data.length>0)
				{
					return data[0];
				}else return null;
		}
		public function getTableContent(order:String=null,where:String=null,whereParams:Array=null):Array{
			var stmt:SQLStatement =adjustBase();
			stmt.text = 'SELECT * FROM main.'+TableName+' ';
			if(where)
			{
				stmt.text+=' where '+where+' ';
			}
			if(order)
			{
				stmt.text+=" order by "+order+ " ";
			}
			
			if(whereParams)
			{
				setParameters(stmt,whereParams);
			}
			
			stmt.execute();
			var data:Array=stmt.getResult().data;
			deActivateCon();
			return data;
		}
		
		protected function adjustBase():SQLStatement
		{
			activateCon();
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConnection;
			stmt.addEventListener(SQLErrorEvent.ERROR,sqlErrorHandler);
			return stmt;
		}
		protected function executeSingleResult(sql:String,params:Array=null,item:Object=null,prop:String=null):Boolean
		{
			var stmt:SQLStatement = adjustBase();
			stmt.text = sql;
			if(params!=null && params.length>0)
				setParameters( stmt, params );
			stmt.execute();
			var son:Boolean= getSingleResultBool(stmt,item,prop);
			deActivateCon();
			return son;
		}
		protected function getSingleResultBool(stmt:SQLStatement,item:Object=null,prop:String=null):Boolean
		{
			var result:SQLResult=stmt.getResult();
			
			if(result==null)return false;
			
			var data:Array;
			if(result!=null)
				data=result.data;
			
			if(data != null && data.length>0 && data[0]!=null)
				return true;
			else if ( result.rowsAffected>0)
			{
				if(item!=null)
					item[prop]=result.lastInsertRowID;
				return true;
			}
			return false;
		}
		protected function setParameters( stmt:SQLStatement, params:Array ):void {
			var param:Object;
			for ( var i:int = 0; i < params.length; i++ ) {
				param = params[i];
				stmt.parameters[ '@' + param.name ] = param.value;
			}
		}	
		protected function sqlErrorHandler( event:SQLError ):void {
			Alert.show( event.message, "Hata oluştu" );
		}
		
	}
}

